﻿using System;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using Microsoft.SharePoint.WebControls;
using Sapiens.at.SharePoint.Settings;
using Microsoft.SharePoint;
using System.Reflection;
using System.Linq;
using System.Collections.Generic;
using Sapiens.at.SharePoint.Extensions;
using System.Data;
using System.Data.SqlClient;

namespace Sapiens.at.SharePoint.SQLM.CONTROLTEMPLATES.Sapiens.at.SharePoint.SQLM
{
    public partial class SQLMReceiverSection : SettingsSection
    {
        #region public properties
        public string Title
        {
            get
            {
                return (ifsReceiverEnabled as InputFormSection).Title;
            }
            set
            {
                (ifsReceiverEnabled as InputFormSection).Title = value;
            }
        }
        public string Description
        {
            get
            {
                return ifsReceiverEnabledDesc.Text;
            }
            set
            {
                ifsReceiverEnabledDesc.Text = value;
            }
        }
        public bool Enabled
        {
            get
            {
                return cbEnabled.Checked;
            }
            set
            {
                cbEnabled.Checked = value;
            }
        }
        private string receivertypename;
        public string ReceiverTypeName
        {
            get
            {
                return receivertypename;
            }
            set
            {
                cbEnabled.Text = SPContext.Current.Web.GetLocalizedString("SQLMEnableReceiver") + " " + value + (Synchronization != SPEventReceiverSynchronization.Default ? " " + Synchronization.ToString() : "");
                receivertypename = value;
            }
        }
        private SPEventReceiverType receivertype;
        /// <summary>
        /// Gets the Name of the ReceiverType, Sets The Name, The Title and the Description
        /// </summary>
        public SPEventReceiverType ReceiverType
        {
            get
            {
                return receivertype;
            }
            set
            {
                receivertype = value;
                ReceiverTypeName = value.ToString();// +(Synchronization != SPEventReceiverSynchronization.Default ? " " + Synchronization.ToString() : "");
                Title = ReceiverTypeName; //SPContext.Current.Web.GetLocalizedString("SQLMReceiverTitle" + ReceiverTypeName);
                Description = SPContext.Current.Web.GetLocalizedString("SQLMReceiverDesc" + ReceiverTypeName + (Synchronization != SPEventReceiverSynchronization.Default ? Synchronization.ToString() : ""));
            }
        }

        private SPEventReceiverSynchronization synchronization = SPEventReceiverSynchronization.Default;
        /// <summary>
        /// Gets the Synchronization of the Receiver, Sets The Name, The Title and the Description
        /// </summary>
        public SPEventReceiverSynchronization Synchronization
        {
            get
            {
                return synchronization;
            }
            set
            {
                synchronization = value;
                ReceiverTypeName = ReceiverType.ToString();// + (synchronization != SPEventReceiverSynchronization.Default ? " " + synchronization.ToString() : "");
                Title = ReceiverTypeName; //SPContext.Current.Web.GetLocalizedString("SQLMReceiverTitle" + ReceiverTypeName);
                Description = SPContext.Current.Web.GetLocalizedString("SQLMReceiverDesc" + ReceiverTypeName + (synchronization != SPEventReceiverSynchronization.Default ? synchronization.ToString() : ""));

            }
        }

        private SPContentType ctype = null;
        /// <summary>
        /// Gets the CType from Request.QueryString[CType] or sets the ContentType
        /// </summary>
        public SPContentType CType
        {
            get
            {
                if (ctype == null && Page.Request != null && !string.IsNullOrEmpty(Page.Request.QueryString["CType"]))
                {
                    ctype = List.ContentTypes[new SPContentTypeId(Page.Request.QueryString["CType"])];
                }
                return ctype;
            }
            set
            {
                ctype = value;
            }
        }

        private SPList list = null;
        /// <summary>
        /// Gets the List from SPContextCurrent.List or sets the List
        /// </summary>
        public SPList List
        {
            get
            {
                if (list == null && SPContext.Current.List != null && Page.Request != null && !string.IsNullOrEmpty(Page.Request.QueryString["List"]))
                {
                    list = SPContext.Current.List;
                }
                return list;
            }
            set
            {
                list = value;
            }
        }

        public Assembly ReceiverAssembly
        {
            get
            {
                return ReceiverClassType.Assembly;
            }
        }

        public Type ReceiverClassType
        {
            get
            {
                if (Synchronization == SPEventReceiverSynchronization.Synchronous)
                    return typeof(Receiver.SQLReceiverSync);
                else
                    return typeof(Receiver.SQLReceiver);
            }
        }

        public string ReceiverClass
        {
            get
            {
                return ReceiverClassType.FullName;
            }
        }
        public string ReceiverName
        {
            get { return ReceiverClass + ReceiverType.ToString() + Synchronization.ToString() + CType.Id.ToString(); }
        }

        private SPEventReceiverDefinition receiverdefinition = null;
        /// <summary>
        /// Gets or Sets the ReceiverDefinition for this Event
        /// </summary>
        public SPEventReceiverDefinition ReceiverDef
        {
            get
            {
                if (receiverdefinition == null)
                {
                    foreach (SPEventReceiverDefinition er in List.EventReceivers)
                    {
                        //find the one
                        if (er.Name == ReceiverName)
                        {
                            receiverdefinition = er;
                        }
                    }
                }
                return receiverdefinition;
            }
            set
            {
                receiverdefinition = value;
            }
        }
        #endregion public properties

        #region private properties
        string CommandTypeSelectedValue
        {
            get
            {
                return !string.IsNullOrEmpty(Page.Request.Form[ddCommandType.UniqueID]) ? Page.Request.Form[ddCommandType.UniqueID] : ddCommandType.SelectedValue; 
            }
        }

        string tbNumParamsOutText
        {
            get
            {
                return !string.IsNullOrEmpty(Page.Request.Form[tbNumParamsOut.UniqueID]) ? Page.Request.Form[tbNumParamsOut.UniqueID] : tbNumParamsOut.Text;
            }
        }
        string tbNumParamsInText
        {
            get
            {
                return !string.IsNullOrEmpty(Page.Request.Form[tbNumParamsIn.UniqueID]) ? Page.Request.Form[tbNumParamsIn.UniqueID] : tbNumParamsIn.Text;
            }
        }

        bool cbEnabledChecked
        {
            get
            {
                return !string.IsNullOrEmpty(Page.Request.Form[cbEnabled.UniqueID]) ? Convert.ToBoolean(Page.Request.Form[cbEnabled.UniqueID]) : cbEnabled.Checked;
            }
        }
        string ddSIUDSelectedValue
        {
            get
            {
                return !string.IsNullOrEmpty(Page.Request.Form[ddSIUD.UniqueID]) ? Page.Request.Form[ddSIUD.UniqueID] : ddSIUD.SelectedValue;
            }
        }
        #endregion

        protected void Page_Load(object sender, EventArgs e)
        {
            cbEnabled.AutoPostBack = true;
            cbEnabled.CheckedChanged += new EventHandler(cbEnabled_CheckedChanged);

            cvConnectionString.ServerValidate += new ServerValidateEventHandler(cvConnectionString_ServerValidate);

            (ifsConnectionString as InputFormSection).Title = SPContext.Current.Web.GetLocalizedString("SQLMifsConnectionString");
            ifsConnectionStringDesc.Text = SPContext.Current.Web.GetLocalizedString("SQLMifsConnectionStringDesc");

            (ifsReceiverDetails as InputFormSection).Title = SPContext.Current.Web.GetLocalizedString("SQLMifsReceiverDetails");
            ifsReceiverDetailsDesc.Text = SPContext.Current.Web.GetLocalizedString("SQLMifsReceiverDetailsDesc");

            labCommandType.Text = SPContext.Current.Web.GetLocalizedString("SQLMlabCommandType");
            ddCommandType.AutoPostBack = true;

            labSIUD.Text = SPContext.Current.Web.GetLocalizedString("SQLMlabSIUD");
            ddSIUD.AutoPostBack = true;
            //ddSIUD.SelectedIndexChanged += new EventHandler(ddSIUD_SelectedIndexChanged);
            
            labNumParamsIn.Text = SPContext.Current.Web.GetLocalizedString("SQLMlabNumParamsIn");
            labNumParamsOut.Text = SPContext.Current.Web.GetLocalizedString("SQLMlabNumParamsOut");

            tbNumParamsIn.AutoPostBack = true;
            //tbNumParamsIn.TextChanged += new EventHandler(tbNumParamsIn_TextChanged);

            tbNumParamsOut.AutoPostBack = true;
            //tbNumParamsOut.TextChanged += new EventHandler(tbNumParamsOut_TextChanged);

            btnTestConnection.Text = SPContext.Current.Web.GetLocalizedString("SQLMtestConnection");
            btnTestConnection.Click += new EventHandler(btnTestConnection_Click);

            if (!Page.IsPostBack)
            {
                ddCommandType.Items.Add(new ListItem(SPContext.Current.Web.GetLocalizedString("SQLMlabCommandTypeText"), CommandType.Text.ToString()));
                ddCommandType.Items.Add(new ListItem(SPContext.Current.Web.GetLocalizedString("SQLMlabCommandTypeStoredProcedure"), CommandType.StoredProcedure.ToString()));

                ddSIUD.Items.Add(new ListItem(SPContext.Current.Web.GetLocalizedString("SQLMSIUDSelect"), SQLMatch.SIUDType.Select.ToString()));
                ddSIUD.Items.Add(new ListItem(SPContext.Current.Web.GetLocalizedString("SQLMSIUDInsert"), SQLMatch.SIUDType.Insert.ToString()));
                ddSIUD.Items.Add(new ListItem(SPContext.Current.Web.GetLocalizedString("SQLMSIUDUpdate"), SQLMatch.SIUDType.Update.ToString()));
                ddSIUD.Items.Add(new ListItem(SPContext.Current.Web.GetLocalizedString("SQLMSIUDDelete"), SQLMatch.SIUDType.Delete.ToString()));

                var receiverData = Convert.ToString(List.RootFolder.Properties[ReceiverName]);
                if (string.IsNullOrEmpty(receiverData)) receiverData = Convert.ToString(CType.ResourceFolder.Properties[ReceiverName]);
                if (!string.IsNullOrEmpty(receiverData))
                {
                    if (ReceiverDef != null) cbEnabled.Checked = true;

                    var xmlData = XmlData.Parse(XmlData.DecodeFrom64(receiverData));
                    ddCommandType.SelectedValue = xmlData.CommandType;
                    ddSIUD.SelectedValue = xmlData.SIUD;
                    tbConnectionString.Text = xmlData.ConnectionString;
                    tbSQLStatement.Text = xmlData.SQLStatement;
                    ddListInValues = xmlData.InFieldParamMapping;
                    ddListOutValues = xmlData.OutFieldParamMapping;
                    ddListInTypes = xmlData.InFieldTypeMapping;
                    ddListOutTypes = xmlData.OutFieldTypeMapping;
                }
                //sets the settings
                //tbImpersonationUser.Text = Get(Key_ImpersonateUser);
            }
            else
            {
                tbNumParamsIn_TextChanged(null, null);
                tbNumParamsOut_TextChanged(null, null);
            }
            cbEnabled_CheckedChanged(null, null);
            ddSIUD_SelectedIndexChanged(null, null);
        }

        void btnTestConnection_Click(object sender, EventArgs e)
        {
            TestConnection();
        }

        void ddSIUD_SelectedIndexChanged(object sender, EventArgs e)
        {
            labNumParamsOut.Visible = (ddSIUDSelectedValue == SQLMatch.SIUDType.Select.ToString());
            tbNumParamsOut.Visible = labNumParamsOut.Visible;
            tableParamsOut.Visible = labNumParamsOut.Visible;
        }

        void SetInputFormSectionEnabled(InputFormSection section)
        {
            section.InputFormControlContainer.FindControls<WebControl>().ToList().ForEach(c => c.Enabled = cbEnabled.Checked);
            section.Visible = cbEnabled.Checked;
        }
        string[] ddListOutValues
        {
            get
            {
                return tbNumParamsOut.Visible ? ddListsOut.Select(dd => dd.SelectedValue).ToArray() : new string[] { };
            }
            set
            {
                if (value != null)
                {
                    tbNumParamsOut.Text = value.Count().ToString();
                    tbNumParamsOut_TextChanged(null, null);
                    int index = 0;
                    foreach (DropDownList dd in ddListsOut) dd.SelectedValue = value[index++];
                }
            }
        }
        string[] ddListOutTypes
        {
            get
            {
                return ddListsOutTypes.Select(dd => dd.SelectedValue).ToArray();
            }
            set
            {
                if (value != null)
                {
                    //tbNumParamsOut.Text = value.Count().ToString();
                    //tbNumParamsOut_TextChanged(null, null);
                    int index = 0;
                    foreach (DropDownList dd in ddListsOutTypes) dd.SelectedValue = value[index++];
                }
            }
        }
        string[] ddListInValues
        {
            get
            {
                return ddListsIn.Select(dd => dd.SelectedValue).ToArray();
            }
            set
            {
                if (value != null)
                {
                    tbNumParamsIn.Text = value.Count().ToString();
                    tbNumParamsIn_TextChanged(null, null);
                    int index = 0;
                    foreach (DropDownList dd in ddListsIn) dd.SelectedValue = value[index++];
                }
            }
        }
        string[] ddListInTypes
        {
            get
            {
                return ddListsInTypes.Select(dd => dd.SelectedValue).ToArray();
            }
            set
            {
                if (value != null)
                {
                    //tbNumParamsIn.Text = value.Count().ToString();
                    //tbNumParamsIn_TextChanged(null, null);
                    int index = 0;
                    foreach (DropDownList dd in ddListsInTypes) dd.SelectedValue = value[index++];
                }
            }
        }

        List<DropDownList> ddListsOut = new List<DropDownList>();
        List<DropDownList> ddListsIn = new List<DropDownList>();
        List<DropDownList> ddListsOutTypes = new List<DropDownList>();
        List<DropDownList> ddListsInTypes = new List<DropDownList>();


        void tbNumParamsOut_TextChanged(object sender, EventArgs e)
        {
            int iout;
            if (int.TryParse(tbNumParamsOutText, out iout) && iout >= 1)
            {
                for (int i = 1; i <= iout; i++)
                {
                    TableRow tr = new TableRow();
                    TableCell tc = new TableCell();
                    tc.Width = Unit.Pixel(240);
                    tc.Controls.Add(new Label() { Text =
                        //SPContext.Current.Web.GetLocalizedString("SQLMvalueOutText") + i.ToString() + " " + SPContext.Current.Web.GetLocalizedString("SQLMparamOutAssign") + ":"
                        SPContext.Current.Web.GetLocalizedString((CommandTypeSelectedValue == CommandType.Text.ToString() ? "SQLMvalueOutText" : "SQLMparamOutText")) + i.ToString() + " " + SPContext.Current.Web.GetLocalizedString("SQLMparamOutAssign") + ":"
                    });
                    tr.Cells.Add(tc);
                    tc = new TableCell();
                    
                    DropDownList dd = CTypeFields(SPContext.Current.Web.GetLocalizedString("SQLMassignOutParamToNoField"), false);
                    dd.ID = "ddOut_" + i;
                    ddListsOut.Add(dd);

                    tc.Controls.Add(dd);
                    tc.Width = Unit.Pixel(240);
                    tr.Cells.Add(tc);

                    tc = new TableCell();
                    tc.Controls.Add(new Label() { Text = SPContext.Current.Web.GetLocalizedString("SQLMasType") });
                    tc.Width = Unit.Pixel(80);
                    tr.Cells.Add(tc);

                    tc = new TableCell();
                    DropDownList ddTypes = CTypeTypes();
                    dd.ID = "ddOutTypes_" + i;
                    ddListsOutTypes.Add(ddTypes);

                    tc.Controls.Add(ddTypes);
                    tc.Width = Unit.Pixel(240);
                    tr.Cells.Add(tc);
                    
                    tableParamsOut.Rows.Add(tr);
                }
            }
        }

        void tbNumParamsIn_TextChanged(object sender, EventArgs e)
        {
            int iout;
            if (int.TryParse(tbNumParamsInText, out iout) && iout >= 1)
            {
                for (int i = 1; i <= iout; i++)
                {
                    TableRow tr = new TableRow();

                    TableCell tc = new TableCell();
                    tc.Width = Unit.Pixel(240);
                    DropDownList dd = CTypeFields(SPContext.Current.Web.GetLocalizedString("SQLMassignInParamFromNoField"), true);
                    dd.ID = "ddIn_" + i;
                    ddListsIn.Add(dd);

                    tc.Controls.Add(dd);
                    tr.Cells.Add(tc);
                    
                    TableCell tc2 = new TableCell();
                    tc2.Controls.Add(new Label() { Text = SPContext.Current.Web.GetLocalizedString("SQLMparamInAssign") + " " + "@in" + i.ToString() });
                    tc2.Width = Unit.Pixel(240);
                    tr.Cells.Add(tc2);

                    TableCell tc3 = new TableCell();
                    tc3.Controls.Add(new Label() { Text = SPContext.Current.Web.GetLocalizedString("SQLMasType") });
                    tc3.Width = Unit.Pixel(80);
                    tr.Cells.Add(tc3);

                    TableCell tc4 = new TableCell();
                    DropDownList ddTypes = CTypeTypes();
                    dd.ID = "ddInTypes_" + i;
                    ddListsInTypes.Add(ddTypes);

                    tc4.Controls.Add(ddTypes);
                    tc4.Width = Unit.Pixel(240);
                    tr.Cells.Add(tc4);
                    
                    tableParamsIn.Rows.Add(tr);
                }
            }
        }

        private DropDownList CTypeFields(string nofield, bool inParams)
        {
            DropDownList dd = new DropDownList();
            dd.Items.Add(new ListItem(nofield));
            foreach (SPField f in CType.ParentList.Fields)
            {
                if ((!f.Hidden
                    & !f.ReadOnlyField
                    & f.Id != SPBuiltInFieldId.ContentType
                    & f.Id != SPBuiltInFieldId.ContentTypeId
                    & f.Type != SPFieldType.Calculated
                    & f.Type != SPFieldType.Computed
                    & f.Type != SPFieldType.ContentTypeId
                    & f.Type != SPFieldType.Lookup)
                    | inParams & f.Type != SPFieldType.Attachments)
                {
                    dd.Items.Add(new ListItem(f.Title, f.InternalName));
                }
            }
            return dd;

        }
        private DropDownList CTypeTypes()
        {
            DropDownList dd = new DropDownList();
            dd.Items.Add(new ListItem(SqlDbType.BigInt.ToString()));
            dd.Items.Add(new ListItem(SqlDbType.Bit.ToString()));
            dd.Items.Add(new ListItem(SqlDbType.Char.ToString()));
            dd.Items.Add(new ListItem(SqlDbType.Date.ToString()));
            dd.Items.Add(new ListItem(SqlDbType.DateTime.ToString()));
            dd.Items.Add(new ListItem(SqlDbType.Decimal.ToString()));
            dd.Items.Add(new ListItem(SqlDbType.Int.ToString()));
            dd.Items.Add(new ListItem(SqlDbType.NVarChar.ToString()));
            dd.Items.Add(new ListItem(SqlDbType.SmallInt.ToString()));
            dd.Items.Add(new ListItem(SqlDbType.Time.ToString()));
            dd.Items.Add(new ListItem(SqlDbType.VarChar.ToString()));
            dd.Items.Add(new ListItem(SqlDbType.Variant.ToString()));
            return dd;
        }

        void cbEnabled_CheckedChanged(object sender, EventArgs e)
        {
            if (cbEnabled.Checked && string.IsNullOrEmpty(tbSQLStatement.Text))
            {
                tbSQLStatement.Text = SPContext.Current.Web.GetLocalizedString("SQLMDefaultStatement");
            }
            //SQLMConnectionDefault
            if (cbEnabled.Checked && string.IsNullOrEmpty(tbConnectionString.Text))
            {
                tbConnectionString.Text = SPContext.Current.Web.GetLocalizedString("SQLMConnectionDefault");
            }
            SetInputFormSectionEnabled((ifsConnectionString as InputFormSection));
            //SetInputFormSectionEnabled((ifsImpersonation as InputFormSection));
            SetInputFormSectionEnabled((ifsReceiverDetails as InputFormSection));
        }

        public override string UserControlPath
        {
            get { return "~/_CONTROLTEMPLATES/" + SP2013.Path + "Sapiens.at.SharePoint.SQLM/SQLMReceiverSection" + SP2013.NameEmptyOr15 + ".ascx"; }
        }
        void cvConnectionString_ServerValidate(object source, ServerValidateEventArgs args)
        {
            if (!string.IsNullOrEmpty(tbConnectionString.Text))
                args.IsValid = TestConnection();
        }

        private bool TestConnection()
        {
            bool RETURN = true;
            bool isOpen = false;
            SqlConnection connection = new SqlConnection(tbConnectionString.Text);
            try
            {
                connection.Open();
                isOpen = true;
            }
            catch (Exception ex)
            {
                RETURN = false;
                cvConnectionString.ErrorMessage = ex.Message;
            }
            finally
            {
                if (isOpen) connection.Close();
            }
            cvConnectionString.IsValid = RETURN;
            return RETURN;
        }

        
        public override void Save()
        {
            Page.Validate();
            if (Page.IsValid)
            {
                var xmlData = new XmlData()
                {
                    ContentType = CType.Id.ToString(),
                    SQLStatement = tbSQLStatement.Text,
                    ConnectionString = tbConnectionString.Text,
                    InFieldParamMapping = ddListInValues,
                    OutFieldParamMapping = ddListOutValues,
                    InFieldTypeMapping = ddListInTypes,
                    OutFieldTypeMapping = ddListOutTypes,
                    CommandType = ddCommandType.SelectedValue,
                    SIUD = ddSIUD.SelectedValue
                };

                if (ReceiverDef != null) ReceiverDef.Delete();
                if (cbEnabled.Checked)
                {
                    ReceiverDef = List.EventReceivers.Add();
                    ReceiverDef.Name = ReceiverName;
                    ReceiverDef.Assembly = ReceiverAssembly.FullName;
                    ReceiverDef.Class = ReceiverClass;
                    ReceiverDef.Type = ReceiverType;
                    ReceiverDef.Synchronization = Synchronization;
                    ReceiverDef.Data = ReceiverName;
                    ReceiverDef.Update();
                }
                List.RootFolder.Properties[ReceiverName] = XmlData.EncodeTo64(xmlData.ToXml());
                List.RootFolder.Update();
                CType.ResourceFolder.Properties[ReceiverName] = XmlData.EncodeTo64(xmlData.ToXml());
                CType.ResourceFolder.Update();
            }
        }
    }
    
}
